package _2工具类;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CategoryDAOImpl implements ICategoryDAO{

	Connection conn;
	Statement stmt;
	public void getconnection() throws SQLException
	{
		// 1. 获取连接
		 conn = DbUtils.getConnection();
		// 2. 查询对象
		 stmt = conn.createStatement();
	}
	
	@Override
	public int insert(Category c) throws SQLException 
	{
		getconnection();
		// 3. 执行查询
		String sql = "insert into category value(null,'"+c.getCname()+"')";
		int result = stmt.executeUpdate(sql);
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, null);		
		return result;
	}

	@Override
	public int deleteById(int cid) throws SQLException 
	{
		getconnection();
		// 3. 执行查询
		String sql = "delete from category where cid="+cid;
		int result = stmt.executeUpdate(sql);
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, null);		
		return result;
	}

	@Override
	public int update(Category c) throws SQLException 
	{
		getconnection();
		// 3. 执行查询
		String sql = "update category set cname='"+c.cname+"'"+" where cid="+c.getCid();
		int result = stmt.executeUpdate(sql);
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, null);		
		return result;
	}

	@Override
	public Category findById(int cid) throws SQLException 
	{
		getconnection();
		// 3. 执行查询
		String sql = "select * from category where cid="+cid;
		ResultSet rs = stmt.executeQuery(sql);
		Category result = null;
		if(rs.next())
		{
			int id = rs.getInt(1);
			String cname = rs.getString(2);
			result = new Category(id,cname);
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);		
		return result;
	}

	@Override
	public List<Category> findAll() throws SQLException 
	{
		List<Category> result = new ArrayList<>();
		getconnection();
		// 3. 执行查询
		String sql = "select * from category";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			int id = rs.getInt(1);
			String cname = rs.getString(2);
			Category c = new Category(id,cname);
			result.add(c);
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);		
		return result;
	}

}
